package com.engine.modules.general.mapper;

import com.engine.common.baseEntity.gis.BoxRangeEntity;
import com.engine.common.exception.MapperException;
import com.engine.common.exception.SystemTableException;
import com.engine.common.util.BasicParamUtil.StringUtils;
import com.engine.init.Globle;
import com.engine.init.entity.TableStatisticalPropertiesEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
@Slf4j
public class GeneralDataCommonMapper {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    /**
     * @Description 通过条件查询数据，存在sql注入风险，不应由controller直接调用
     * @param tableName
     * @param whereStr
     * @param authCo
     * @Return
    */
    public List<Map<String, Object>> selectDataByWhereStr(String tableName, String whereStr, String authCo) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String general_columns = (String) Globle.TablePropertiesMap.get(tableName).getGeneralColumns();
        String sys_columns = (String) Globle.TablePropertiesMap.get(tableName).getSysColumns();
        String colums = general_columns + "," + sys_columns;
        String sql = "select " + colums +
                " from " + tableName +
                " where " + whereStr + " AND " + "(LEFT(pac," + authCo.length() + ")='" + authCo + "' OR pac IS NULL)";
        List<Map<String, Object>> rsList = jdbcTemplate.queryForList(sql);
        return rsList;
    }

    public String updateDataByColumn(String tableName,String id, String column,Object data) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new NullPointerException("数据库中表不存在或未添加进数据目录");
        String sql = "UPDATE " + tableName +
                " SET " + column + "=?"+
                " WHERE id='"+id+"'";
       int i = jdbcTemplate.update(sql,data);
       if(i>0)
            return id;
       else
           return "";
    }

    public List<Map<String, Object>> selectStatisticalData(String statisticalNames, String authCo) {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("authCoLenth", authCo.length());
        mapArgs.put("authCo", authCo);
        String sql = "";
        String sqlWhere = "(LEFT(pac,:authCoLenth)=:authCo OR pac IS NULL)";
        List<Map<String, Object>> rs = new ArrayList<>();
        Map<String, Object> endMap = new HashMap<>();
        String[] statisticalNameArry = statisticalNames.split(",");
        for (int i = 0; i < statisticalNameArry.length; i++) {
            TableStatisticalPropertiesEntity tsp = Globle.TableStatisticalPropertiesMap.get(statisticalNames);
            if (tsp == null)
                continue;
            sql = tsp.getSql();
            sql = sql.replace("(3=3)", sqlWhere);
            List<Map<String, Object>> maps = namedParameterJdbcTemplate.queryForList(sql, mapArgs);
            endMap.put("statistical_name", statisticalNames);
            endMap.put("statisticalTableName", tsp.getTableName());
            endMap.put("authCo", authCo);
            endMap.put("result", maps);
            rs.add(endMap);
        }
        return rs;
    }


    public List<Map<String, Object>> selectStatisticalDataByGeometry(String statisticalNames, String geometry, String authCo) throws Exception {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("authCoLenth", authCo.length());
        mapArgs.put("authCo", authCo);
        mapArgs.put("geometry", geometry);
        String sql = "";
        String sqlWhere = "(LEFT(pac,:authCoLenth)=:authCo OR pac IS NULL) AND ST_Intersects(geom,st_geomfromgeojson(:geometry))";
        List<Map<String, Object>> rs = new ArrayList<>();
        String[] statisticalNameArry = statisticalNames.split(",");
        for (int i = 0; i < statisticalNameArry.length; i++) {
            TableStatisticalPropertiesEntity tsp = Globle.TableStatisticalPropertiesMap.get(statisticalNames);
            if (tsp == null)
                continue;
            sql = tsp.getSql();
            sql = sql.replace("(3=3)", sqlWhere);
            Map<String, Object> rsMap = namedParameterJdbcTemplate.queryForMap(sql, mapArgs);
            Map<String, Object> endMap = new HashMap<>();
            endMap.put("statisticalName", statisticalNameArry[i]);
            endMap.put("statisticalTableName", tsp.getTableName());
            endMap.put("value", rsMap.get("value"));
            endMap.put("type", "statisticalValue");
            rs.add(endMap);
        }
        return rs;
    }

    public Map<String, Object> selectTableInfo(String tableName) {
        String sql = "SELECT pp.tablename,gg.srid,gg.type,gg.coord_dimension,cc.comment FROM pg_tables AS pp LEFT JOIN " +
                "geometry_columns AS gg ON pp.tablename=gg.f_table_name LEFT JOIN (select relname as tabname," +
                "obj_description(relname::regclass, 'pg_class') as comment from pg_class c where  relkind = 'r' and " +
                "relname not like 'pg_%' and relname not like 'sql_%' order by relname ) AS cc ON pp.tablename=cc" +
                ".tabname WHERE pp.schemaname='public' AND pp.tablename =?";
        Map<String, Object> tableInfo = jdbcTemplate.queryForMap(sql, tableName);
        sql = "SELECT A.attname as name,format_type(a.atttypid,a.atttypmod) as type,col_description(a.attrelid,a" +
                ".attnum) as comment FROM pg_catalog.pg_attribute A WHERE A.attrelid = ( SELECT oid FROM pg_class " +
                "WHERE relname = ? )  AND A.attnum > 0  AND NOT A.attisdropped ORDER BY A.attnum";
        List<Map<String, Object>> columnsInfo = jdbcTemplate.queryForList(sql, tableName);
        sql = "SELECT id,statistical_name,table_name,description,sql FROM sys_table_statistical_properties WHERE table_name=?";
        List<Map<String, Object>> statisticalInfo = jdbcTemplate.queryForList(sql, tableName);
        Map<String, Object> info = new HashMap<>();
        info.put("table_name", tableName);
        info.put("table_info", tableInfo);
        info.put("columns_info", columnsInfo);
        info.put("statistical_info", statisticalInfo);
        return info;
    }

    public Map<String, Object> selectLocationByGeometry(String geometry) {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("geometry", geometry);
        String sql = "SELECT pac,name,location_lvl FROM tob_gis_polygon_location_all WHERE st_intersects(st_centroid(st_geomfromgeojson( :geometry )" +
                "), geom)";
        List<Map<String, Object>> mapList = namedParameterJdbcTemplate.queryForList(sql, mapArgs);
        Map<String, Object> rsMap = new HashMap<>();
        for (Map<String, Object> map : mapList) {
            rsMap.put(map.get("location_lvl").toString(), map);
        }
        return rsMap;
    }

    public <T> String updateByEntity(T entity, String keyName, String tableName) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, MapperException {
        Map<String, Object> map= PropertyUtils.describe(entity);
        if(map.get(keyName)==null)
            throw new MapperException("entity中不包含keyName的参数");
        String start = "UPDATE "+tableName+" SET ";
        String content="";
        String condition="";
        String successId="";
        List<Object> list=new ArrayList<>();
        for (String key:map.keySet()) {
            if(StringUtils.humpToUnderline(key).equals(StringUtils.humpToUnderline(keyName)))
            {
                condition=" WHERE "+StringUtils.humpToUnderline(key)+"=:"+key;
                successId=(String) map.get(key);
                continue;
            }
            if(map.get(key)!=null)
            {
                if(key.equals("table_pac"))
                    continue;
                content+=StringUtils.humpToUnderline(key)+"=:"+key+",";
                list.add(map.get(key));
            }
        }
        if(content.equals("")||condition.equals(""))
            throw new MapperException("没有更新内容或key错误");
        content+="gmt_update=NOW()";
        String sql=start+content+condition;
        int i=0;
        try {
            i=namedParameterJdbcTemplate.update(sql,map);
        }catch (Exception e)
        {
            log.debug(e.getMessage());
        }
        if(i>0)
            return successId;
        else
            return "";
    }

    public Double selectShapeAreaByGeometry(String geometry) {
        Map<String, Object> mapArgs = new HashMap<String, Object>();
        mapArgs.put("geometry", geometry);
        String sql = "SELECT st_area(ST_Transform(st_geomfromgeojson(:geometry),4521)) as shape_area";
        Map<String, Object> rsList = namedParameterJdbcTemplate.queryForMap(sql, mapArgs);
        return Double.parseDouble(rsList.get("shape_area").toString());
    }

    public BoxRangeEntity selectBoxRangeById(String tableName, String id)
    {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new SystemTableException("tableName="+tableName+",数据库中表不存在或未添加进数据目录");
        BoxRangeEntity boxRangeEntity=new BoxRangeEntity();
        String sql="SELECT st_xmin(st_transform(geom, 3857))," +
                "st_ymin(st_transform(geom, 3857))," +
                "st_xmax(st_transform(geom, 3857))," +
                "st_ymax(st_transform(geom, 3857)) " +
                "FROM " +tableName+
                " WHERE id=?";
        try{
            Map<String, Object> map=jdbcTemplate.queryForMap(sql);
            boxRangeEntity.setMinX((Double)map.get("st_xmin"));
            boxRangeEntity.setMaxX((Double)map.get("st_xmax"));
            boxRangeEntity.setMinY((Double)map.get("st_ymin"));
            boxRangeEntity.setMaxY((Double)map.get("st_ymax"));
            return  boxRangeEntity;
        }catch (Exception e) {
            return null;
        }
    }

    public BoxRangeEntity selectBoxRangeByWhereStr(String tableName, String whereStr)
    {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new SystemTableException("tableName="+tableName+",数据库中表不存在或未添加进数据目录");
        BoxRangeEntity boxRangeEntity=new BoxRangeEntity();
        String sql="SELECT st_xmin(st_transform(geom, 3857))," +
                "st_ymin(st_transform(geom, 3857))," +
                "st_xmax(st_transform(geom, 3857))," +
                "st_ymax(st_transform(geom, 3857)) " +
                "FROM " +tableName+
                " WHERE (3=3) AND "+whereStr;
        try{
            Map<String, Object> map=jdbcTemplate.queryForMap(sql);
            boxRangeEntity.setMinX((Double)map.get("st_xmin"));
            boxRangeEntity.setMaxX((Double)map.get("st_xmax"));
            boxRangeEntity.setMinY((Double)map.get("st_ymin"));
            boxRangeEntity.setMaxY((Double)map.get("st_ymax"));
            return  boxRangeEntity;
        }catch (Exception e) {
            return null;
        }
    }


    public String selectTfIdcById(String tableName, String id) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new SystemTableException("tableName=" + tableName + ",数据库中表不存在或未添加进数据目录");
        BoxRangeEntity boxRangeEntity = new BoxRangeEntity();
        String sql = "SELECT tf_idc" +
                " FROM " + tableName +
                " WHERE id=?";
        List<String> str = jdbcTemplate.queryForList(sql, String.class, id);
        if (str.size() > 0)
            return str.get(0);
        else
            return "";
    }

    public String selectTfIdcByFbId(String tableName, String fbId) {
        if (Globle.TablePropertiesMap.get(tableName) == null)
            throw new SystemTableException("tableName=" + tableName + ",数据库中表不存在或未添加进数据目录");
        BoxRangeEntity boxRangeEntity = new BoxRangeEntity();
        String sql = "SELECT tf_idc" +
                " FROM " + tableName +
                " WHERE field_black_no=?";
        List<String> str = jdbcTemplate.queryForList(sql, String.class, fbId);
        if (str.size() > 0)
            return str.get(0);
        else
            return "";
    }


}


